Re: [GENERAL] Any ideas why this doesn't work or how to rewrite it?

At 21:04 +0300 on 29/04/1999, Brett W. McCoy wrote:


> I think, Aaron, you could get a count of distinct customer names like this:
>
> SELECT DISTINCT customer_username, COUNT(*) FROM customerdata
> GROUP BY customer_username;
>
> This will give you 2 columns, one with the distinct customer_usernames
> and the second with the count of each.  The GROUP BY caluse is important
> here.  This looks like what you wanted in your original query.

No, Brett. COUNT( DISTINCT ...) is supposed to count the number of distinct
names in a table. Here, I created a test table:

testing=> select * from test;
customer
--------
moshe
david
hanna
david
sarah
moshe
suzanne
moshe
moshe
(9 rows)

The distinct names are:

testing=> select distinct customer
testing-> from test;
customer
--------
david
hanna
moshe
sarah
suzanne
(5 rows)

So clearly, the datum he wanted was "5" - there are five distinct customers
here.

Your query, however, gives the following:

testing=> select distinct customer, count(*)
testing-> from test
testing-> group by customer;
customer|count
--------+-----
david   |    2
hanna   |    1
moshe   |    4
sarah   |    1
suzanne |    1
(5 rows)

Which shows him the number of REPETITIONS on each distinct name.

My ugly query gives:

testing=> select count(*)
testing-> from test t1
testing-> where int( oid ) = (
testing->   SELECT min( int( t2.oid ) )
testing->   FROM test t2
testing->   WHERE t2.customer = t1.customer
testing-> );
count
-----
    5
(1 row)

And this is the exact number of distinct names in the table.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma